package com.chinatsp.dao.impl;

import com.chinatsp.dao.ISelectDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author 高杰 @date 20160822
 * @类的名称
 * @description
 * @modify @修改日期 ${date} @修改内容
 */
@Repository
public class ImplSelectDAO implements ISelectDAO {

    private Logger log = LoggerFactory.getLogger(ImplSelectDAO.class);

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 2.3.1 获取在线新闻频道
     *
     * @return
     */
    public List<Map<String, Object>> queryNewsChannel(String pos, String cnt) {
        String sql = "SELECT c.channel_id,c.channel_name FROM carpad_news_channel c LIMIT " + pos
                + "," + cnt;
        log.info(sql);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    /**
     * 根据id查询新闻详细信息
     *
     * @param news_id
     * @return
     */
    public List<Map<String, Object>> getNewsInfo(String news_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT cni.news_id,cni.news_title,cni.news_content,cni.news_source,cni" +
				".pubdate,cnt.channel_name")
                .append(" FROM carpad_news_info cni left join carpad_news_title cnt")
                .append(" ON cni.news_id=cnt.news_id")
                .append(" WHERE cni.news_id = '").append(news_id).append("'");

        log.info("根据id查询新闻详细信息：=====>SQl " + sql.toString());
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
        return list;
    }

    /**
     * 2.3.2	根据新闻频道ID统计获取新闻数量
     *
     * @param channelID
     * @return
     */
    public int countNewsListById(String channelID) {
        String sql = "select COUNT(*) countnews from carpad_news_title AS t, carpad_news_info AS " +
				"n WHERE t.channel_id = '" + channelID + "' AND t.news_id = " +
                "n.news_id AND n.news_content != '' and t.reviewed_status = '1' ORDER BY n" +
				".pubdate DESC";
        int count = Integer.parseInt(jdbcTemplate.queryForMap(sql).get("countnews").toString());
        return count;
    }

    /**
     * 2.3.2	获取新闻信息列表
     *
     * @param channelID
     * @param pos
     * @param cnt
     * @return
     */
    public List<Map<String, Object>> queryNewsList(String channelID, String pos, String cnt) {
        String sql = "SELECT t.channel_name, t.news_id, t.news_title, t.news_url, t.create_time,n" +
				".pubdate,n.news_source" +
                " FROM carpad_news_title AS t, carpad_news_info AS n WHERE t.channel_id = '" +
				channelID + "' AND t.news_id = " +
                "n.news_id AND n.news_content != '' and t.reviewed_status = '1' ORDER BY n.pubdate DESC ";

        int start = Integer.parseInt(pos);
        int count = Integer.parseInt(cnt);    //返回的条数

        sql = sql + "LIMIT " + start + "," + count;
        log.info(sql);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }


}
